{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "NOTE:\n",
    "-----\n",
    "\n",
    "Please run the below cells first before proceeding- you'll need them soon!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql sqlite://"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "pragma foreign_keys = ON; -- WARNING: by default off in sqlite\n",
    "drop table if exists product; -- This needs to be dropped if exists, see why further down!\n",
    "drop table if exists company;\n",
    "create table company (\n",
    "    cname varchar primary key, -- company name uniquely identifies the company.\n",
    "    stockprice money, -- stock price is in money \n",
    "    country varchar); -- country is just a string\n",
    "insert into company values ('GizmoWorks', 25.0, 'USA');\n",
    "insert into company values ('Canon', 65.0, 'Japan');\n",
    "insert into company values ('Hitachi', 15.0, 'Japan');\n",
    "create table product(\n",
    "       pname varchar primary key, -- name of the product\n",
    "       price money, -- price of the product\n",
    "       category varchar, -- category\n",
    "       manufacturer varchar, -- manufacturer\n",
    "       foreign key (manufacturer) references company(cname));\n",
    "insert into product values('Gizmo', 19.99, 'Gadgets', 'GizmoWorks');\n",
    "insert into product values('SingleTouch', 149.99, 'Photography', 'Canon');\n",
    "insert into product values('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks');\n",
    "insert into product values('MultiTouch', 203.99, 'Household', 'Hitachi');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS franchise;\n",
    "CREATE TABLE franchise (name TEXT, db_type TEXT);\n",
    "INSERT INTO franchise VALUES ('Bobs Bagels', 'NoSQL');\n",
    "INSERT INTO franchise VALUES ('eBagel', 'NoSQL');\n",
    "INSERT INTO franchise VALUES ('BAGEL CORP', 'MySQL');\n",
    "\n",
    "DROP TABLE IF EXISTS store;\n",
    "CREATE TABLE store (franchise TEXT, location TEXT);\n",
    "INSERT INTO store VALUES ('Bobs Bagels', 'NYC');\n",
    "INSERT INTO store VALUES ('eBagel', 'PA');\n",
    "INSERT INTO store VALUES ('BAGEL CORP', 'Chicago');\n",
    "INSERT INTO store VALUES ('BAGEL CORP', 'NYC');\n",
    "INSERT INTO store VALUES ('BAGEL CORP', 'PA');\n",
    "\n",
    "DROP TABLE IF EXISTS bagel;\n",
    "CREATE TABLE bagel (name TEXT, price MONEY, made_by TEXT);\n",
    "INSERT INTO bagel VALUES ('Plain with shmear', 1.99, 'Bobs Bagels');\n",
    "INSERT INTO bagel VALUES ('Egg with shmear', 2.39, 'Bobs Bagels');\n",
    "INSERT INTO bagel VALUES ('eBagel Drinkable Bagel', 27.99, 'eBagel');\n",
    "INSERT INTO bagel VALUES ('eBagel Expansion Pack', 1.99, 'eBagel');\n",
    "INSERT INTO bagel VALUES ('Plain with shmear', 0.99, 'BAGEL CORP');\n",
    "INSERT INTO bagel VALUES ('Organic Flax-seed bagel chips', 0.99, 'BAGEL CORP');\n",
    "\n",
    "DROP TABLE IF EXISTS purchase;\n",
    "-- Note that date is an int here just to simplify things\n",
    "CREATE TABLE purchase (bagel_name TEXT, franchise TEXT, date INT, quantity INT, purchaser_age INT);\n",
    "INSERT INTO purchase VALUES ('Plain with shmear', 'Bobs Bagels', 1, 12, 28);\n",
    "INSERT INTO purchase VALUES ('Egg with shmear', 'Bobs Bagels', 2, 6, 47);\n",
    "INSERT INTO purchase VALUES ('Plain with shmear', 'BAGEL CORP', 2, 12, 24);\n",
    "INSERT INTO purchase VALUES ('Plain with shmear', 'BAGEL CORP', 3, 1, 17);\n",
    "INSERT INTO purchase VALUES ('eBagel Expansion Pack', 'eBagel', 1, 137, 5);\n",
    "INSERT INTO purchase VALUES ('Plain with shmear', 'Bobs Bagels', 4, 24, NULL);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Order By Reprise\n",
    "================\n",
    "* SQL-89 forbids the following, but we can make sense of it:\n",
    "> SELECT pname FROM Product ORDER BY Price\n",
    "\n",
    "Let's try out some other slightly unintuitive ORDER BY queries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>pname</th>\n",
       "        <th>price</th>\n",
       "        <th>category</th>\n",
       "        <th>manufacturer</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gizmo</td>\n",
       "        <td>19.99</td>\n",
       "        <td>Gadgets</td>\n",
       "        <td>GizmoWorks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SingleTouch</td>\n",
       "        <td>149.99</td>\n",
       "        <td>Photography</td>\n",
       "        <td>Canon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PowerGizmo</td>\n",
       "        <td>29.99</td>\n",
       "        <td>Gadgets</td>\n",
       "        <td>GizmoWorks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MultiTouch</td>\n",
       "        <td>203.99</td>\n",
       "        <td>Household</td>\n",
       "        <td>Hitachi</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Gizmo', 19.99, 'Gadgets', 'GizmoWorks'),\n",
       " ('SingleTouch', 149.99, 'Photography', 'Canon'),\n",
       " ('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks'),\n",
       " ('MultiTouch', 203.99, 'Household', 'Hitachi')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM Product;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>pname</th>\n",
       "        <th>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gizmo</td>\n",
       "        <td>19.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MultiTouch</td>\n",
       "        <td>203.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PowerGizmo</td>\n",
       "        <td>29.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SingleTouch</td>\n",
       "        <td>149.99</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Gizmo', 19.99),\n",
       " ('MultiTouch', 203.99),\n",
       " ('PowerGizmo', 29.99),\n",
       " ('SingleTouch', 149.99)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT pname,price FROM Product\n",
    "ORDER BY pname"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>pname</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gizmo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PowerGizmo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SingleTouch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MultiTouch</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Gizmo',), ('PowerGizmo',), ('SingleTouch',), ('MultiTouch',)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT pname FROM Product\n",
    "ORDER BY Price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>pname</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gizmo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>PowerGizmo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SingleTouch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MultiTouch</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Gizmo',), ('PowerGizmo',), ('SingleTouch',), ('MultiTouch',)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT distinct pname FROM Product\n",
    "ORDER BY Price"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "* Some engines- like Postgres- would balk at this, but some engines- like SQLite- do process this... how?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Set Operators\n",
    "=======\n",
    "We generate three tables below:\n",
    "* R is {1,2,3,4,5}\n",
    "* S is {}\n",
    "* T is {1,4,7,10}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      " * sqlite://\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      " * sqlite://\n",
      "1 rows affected.\n",
      " * sqlite://\n",
      "1 rows affected.\n",
      " * sqlite://\n",
      "1 rows affected.\n",
      " * sqlite://\n",
      "1 rows affected.\n",
      " * sqlite://\n",
      "1 rows affected.\n",
      " * sqlite://\n",
      "1 rows affected.\n",
      " * sqlite://\n",
      "1 rows affected.\n",
      " * sqlite://\n",
      "1 rows affected.\n",
      " * sqlite://\n",
      "1 rows affected.\n"
     ]
    }
   ],
   "source": [
    "# Create tables & insert some random numbers\n",
    "# Note: in Postgresql, try the generate_series function...\n",
    "%sql DROP TABLE IF EXISTS R; DROP TABLE IF EXISTS S; DROP TABLE IF EXISTS T;\n",
    "%sql CREATE TABLE R (A int); CREATE TABLE S (A int); CREATE TABLE T (A int);\n",
    "for i in range(1,6):\n",
    "    %sql INSERT INTO R VALUES (:i)\n",
    "for i in range(1,11,3):\n",
    "    %sql INSERT INTO T VALUES (:i)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Let's try to get $R \\cap (S \\cup T) = \\{1,4\\}$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>A</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT DISTINCT R.A FROM R, S, T\n",
    "WHERE R.A=S.A OR R.A=T.A"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**Why is the return set empty?**\n",
    "\n",
    "*Recall the order of operations for this query:*\n",
    "1. We take the cross-product of `R,S,T`\n",
    "2. We filter this intermediate table by the conditions of the `WHERE` clause.  \n",
    "\n",
    "Let's see (1):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>A</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT R.A FROM R, S, T;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "The cross-product query is empty because `S` is empty!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Union\n",
    "-----\n",
    "\n",
    "Let's try a different way using a new SQL command, `UNION`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>A</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1,), (4,)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT R.A FROM R, S WHERE R.A=S.A\n",
    "UNION ALL-- this is an explicit keyword!\n",
    "SELECT R.A FROM R, T WHERE R.A=T.A"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "* There are no duplicates (union is a set)\n",
    "* If we wanted duplicates, we could use UNION ALL\n",
    "  * R = {1,2,3,4,5}\n",
    "  * S = {1,2,3,4,5}\n",
    "  * T = {1,4,7,10}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n",
      "Done.\n",
      " * sqlite://\n",
      "1 rows affected.\n",
      " * sqlite://\n",
      "1 rows affected.\n",
      " * sqlite://\n",
      "1 rows affected.\n",
      " * sqlite://\n",
      "1 rows affected.\n",
      " * sqlite://\n",
      "1 rows affected.\n"
     ]
    }
   ],
   "source": [
    "%sql DROP TABLE IF EXISTS S; CREATE TABLE S (A int);\n",
    "for i in range(1,6):\n",
    "    %sql INSERT INTO S VALUES (:i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>A</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1,), (2,), (3,), (4,), (5,), (1,), (4,)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- UNION ALL example- notice that 1 and 4 occur twice!\n",
    "SELECT R.A FROM R, S WHERE R.A=S.A\n",
    "UNION ALL\n",
    "SELECT R.A FROM R, T WHERE R.A=T.A"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Other Set Operations: INTERSECT, EXCEPT\n",
    "-------------------------------------\n",
    "\n",
    "Two examples below... see more in the next section"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>A</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1,), (4,)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT R.A FROM R, S, T WHERE R.A = S.A\n",
    "INTERSECT\n",
    "SELECT R.A FROM R, S, T WHERE R.A = T.A"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>A</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2,), (3,), (5,)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT R.A FROM R, S, T WHERE R.A = S.A\n",
    "EXCEPT\n",
    "SELECT R.A FROM R, S, T WHERE R.A = T.A"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Running example for rest of this notebook: BAGELS\n",
    "===========================\n",
    "\n",
    "The selection of which had nothing to do with how hungry the person writing this section was.\n",
    "\n",
    "Outline of topics:\n",
    "* Nested queries: motivation & examples\n",
    "* Aggregation: Overview, with group-by\n",
    "* Null Values, Outer vs. Inner Joins"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Story:\n",
    "------\n",
    "* *eBagel* is a ground-breaking new tech startup in the red-hot NoSQL-based bagel enterprise space\n",
    "* eBagel has just received $100M in venture capital funding.  However, their sales numbers are dropping off, and you have been brought in to analyze the data and figure out what's going wrong"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Let's begin by populating the following tables:\n",
    "> Franchise(name TEXT, db_type TEXT)\n",
    "\n",
    "> Store(franchise TEXT, location TEXT)\n",
    "\n",
    "> Bagel(name TEXT, price MONEY, made_by TEXT)\n",
    "\n",
    "> Purchase(bagel_name TEXT, franchise TEXT, date INT, quantity INT, purchaser_age INT)\n",
    "\n",
    "Let's quickly load the data first"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Union\n",
    "-----\n",
    "\n",
    "First, let's find franchises that have locations in PA **OR** NYC to see who the potential competition is:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>franchise</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BAGEL CORP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bobs Bagels</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>eBagel</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('BAGEL CORP',), ('Bobs Bagels',), ('eBagel',)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT franchise FROM store WHERE location = 'NYC'\n",
    "UNION\n",
    "SELECT franchise FROM store WHERE location = 'PA';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Intersect: Subtle problem...\n",
    "--------------------------\n",
    "\n",
    "eBagel's CEO is curious to know more about the back-end technologies of Bagel companies that have successfully spread across multiple locations.  Let's try to use an `INTERSECT` operator to find the database types of franchises which have stores in PA **AND** NYC:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>db_type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MySQL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NoSQL</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('MySQL',), ('NoSQL',)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT f.db_type\n",
    "FROM franchise f, store s \n",
    "WHERE f.name = s.franchise AND s.location = 'NYC'\n",
    "INTERSECT\n",
    "SELECT f.db_type\n",
    "FROM franchise f, store s \n",
    "WHERE f.name = s.franchise AND s.location = 'PA'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "*What happened here?*\n",
    "\n",
    "If we look at the data, we should only have gotten \"MySQL\" as a result:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>location</th>\n",
       "        <th>db_type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>NYC</td>\n",
       "        <td>NoSQL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>eBagel</td>\n",
       "        <td>PA</td>\n",
       "        <td>NoSQL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BAGEL CORP</td>\n",
       "        <td>Chicago</td>\n",
       "        <td>MySQL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BAGEL CORP</td>\n",
       "        <td>NYC</td>\n",
       "        <td>MySQL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BAGEL CORP</td>\n",
       "        <td>PA</td>\n",
       "        <td>MySQL</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bobs Bagels', 'NYC', 'NoSQL'),\n",
       " ('eBagel', 'PA', 'NoSQL'),\n",
       " ('BAGEL CORP', 'Chicago', 'MySQL'),\n",
       " ('BAGEL CORP', 'NYC', 'MySQL'),\n",
       " ('BAGEL CORP', 'PA', 'MySQL')]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT f.name, s.location, f.db_type\n",
    "FROM franchise f, store s \n",
    "WHERE f.name = s.franchise;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "So what happened?  We can see why this occurs by breaking the query operation down:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>db_type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NoSQL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MySQL</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('NoSQL',), ('MySQL',)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT f.db_type \n",
    "FROM franchise f, store s \n",
    "WHERE f.name = s.franchise AND s.location = 'NYC'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>db_type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NoSQL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MySQL</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('NoSQL',), ('MySQL',)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT f.db_type\n",
    "FROM franchise f, store s\n",
    "WHERE f.name = s.franchise AND s.location = 'PA'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Essentially, the problem is that we did the `INTERSECT` operation over the return attributes, not the attributes we actually wanted to use!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Nested queries\n",
    "========\n",
    "\n",
    "Instead, let's see one solution to the above problem that uses *nested queries*: i.e. subqueries that return relations which our main query then operates over:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>db_type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MySQL</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('MySQL',)]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT f.db_type\n",
    "FROM franchise f\n",
    "WHERE f.name IN (\n",
    "    SELECT s.franchise FROM store s WHERE s.location = 'NYC')\n",
    "  AND f.name IN (\n",
    "    SELECT s.franchise FROM store s WHERE s.location = 'PA');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Another nested queries example: eBagel's CEO wants to know what databases support bagel companies that appeal to to the crucial twenty-something demographic:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>db_type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NoSQL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MySQL</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('NoSQL',), ('MySQL',)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT f.db_type\n",
    "FROM franchise f\n",
    "WHERE f.name IN (\n",
    "    SELECT b.made_by\n",
    "    FROM bagel b, purchase p\n",
    "    WHERE b.name = p.bagel_name \n",
    "      AND p.purchaser_age >= 20 AND p.purchaser_age < 30);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Nested queries are powerful and convenient!  Could we accomplish the above without them however?  Would this return the same result?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>db_type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MySQL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NoSQL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MySQL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>NoSQL</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('MySQL',), ('NoSQL',), ('MySQL',), ('NoSQL',)]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT f.db_type\n",
    "FROM franchise f, bagel b, purchase p\n",
    "WHERE f.name = b.made_by \n",
    "  AND b.name = p.bagel_name \n",
    "  AND p.purchaser_age >= 20 AND p.purchaser_age < 30;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "**Beware of duplicates!**  We can add a `DISTINCT` however and they will be equivalent- try this for yourself."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "We can also use the following over the results of nested subqueries:\n",
    "* `ALL`\n",
    "* `ANY`\n",
    "* `EXISTS`\n",
    "\n",
    "**Unfortunately, `ALL` and `ANY` are not supported in SQLite (the DBMS we're using here)- see the lecture notes for examples.**  However we can show an example of `EXISTS`.  Suppose we want to answer the following question: does eBagel have *any* products which are cheaper than *any* of its competitors'?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>eBagel Expansion Pack</td>\n",
       "        <td>1.99</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('eBagel Expansion Pack', 1.99)]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT b.name, b.price\n",
    "FROM bagel b\n",
    "WHERE b.made_by = 'eBagel'\n",
    "  AND EXISTS (SELECT name FROM bagel WHERE made_by <> 'eBagel' AND price > b.price);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Note that this is a *correlated query* because values from the main outer query are involved in the inner nested query.  Especially take notice of the variable scoping here.  Note also that this can be expressed as a single SFW query- can you figure out how?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Summary so far:\n",
    "--------------\n",
    "\n",
    "SQL:\n",
    "* Provides a high-level declarative language for manipulating data (DML)\n",
    "* The workhorse is the SFW block\n",
    "* Set operators are powerful- but have some subtleties to be aware of!\n",
    "* Powerful, nested queries are also allowed\n",
    "    * **Note that this becomes especially helpful when using a more full-featured DBMS than SQLite!**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aggregation\n",
    "=======\n",
    "\n",
    "SQL supports several aggregation operations:\n",
    "* `SUM`\n",
    "* `COUNT`\n",
    "* `AVG`\n",
    "* `MIN`\n",
    "* `MAX`\n",
    "\n",
    "*Except `COUNT`*, all aggregation operators apply to a single attribute only"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here are some simple examples:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>AVG(price)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14.989999999999998</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(14.989999999999998,)]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT AVG(price) FROM bagel WHERE made_by = 'eBagel';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that you can define the attribute labels of the output schema, which is convenient for aggregation operations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Number of Stores in PA</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2,)]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(*) AS \"Number of Stores in PA\" FROM store WHERE location = 'PA';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What if we want to know how many total locations there are of relevance in the Bagel 2.0 space?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>COUNT(location)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(5,)]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(location) FROM store;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What we actually wanted:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>COUNT(DISTINCT location)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3,)]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT COUNT(DISTINCT location) FROM store;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, you've purchased some industry sales data, and want to get a sense of how big the bagel space is exactly.  How can we do this easily and efficiently, *all in SQL*?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>net_sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>432.99</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(432.99,)]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(b.price * p.quantity) AS net_sales\n",
    "FROM bagel b, purchase p\n",
    "WHERE b.name = p.bagel_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's a huge market opportunity!  Now, eBagel's CEO is quite excited, and wants to see more detail, such as how the distribution across companies breaks down.  Luckily, **grouping and aggregation can be combined!**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>made_by</th>\n",
       "        <th>revenue</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BAGEL CORP</td>\n",
       "        <td>12.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>85.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>eBagel</td>\n",
       "        <td>272.63</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('BAGEL CORP', 12.87), ('Bobs Bagels', 85.98), ('eBagel', 272.63)]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT b.made_by, SUM(b.price * p.quantity) AS revenue\n",
    "FROM bagel b, purchase p\n",
    "WHERE b.made_by = p.franchise AND b.name = p.bagel_name\n",
    "GROUP BY b.made_by;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These are exciting results!\n",
    "\n",
    "Finally, to cap off the analysis, you want to see the distribution of revenue by product.  *However*, in order to paint a more effective high-level picture, you only want to see best-sellers, which you define as products with more that a dozen total sales.\n",
    "\n",
    "To do this we introduce the `HAVING` clause, which conditions *on aggregates*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>84.50999999999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>eBagel Expansion Pack</td>\n",
       "        <td>272.63</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Plain with shmear', 84.50999999999999), ('eBagel Expansion Pack', 272.63)]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT b.name, SUM(b.price * p.quantity) AS sales\n",
    "FROM bagel b, purchase p\n",
    "WHERE b.name = p.bagel_name AND b.made_by = p.franchise\n",
    "GROUP BY b.name\n",
    "HAVING SUM(p.quantity) > 12;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's break down this last query into its actual evaluation steps:\n",
    "\n",
    "First, the FROM-WHERE segment of the clause is evaluated:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>price</th>\n",
       "        <th>made_by</th>\n",
       "        <th>bagel_name</th>\n",
       "        <th>franchise</th>\n",
       "        <th>date</th>\n",
       "        <th>quantity</th>\n",
       "        <th>purchaser_age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>1.99</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>1</td>\n",
       "        <td>12</td>\n",
       "        <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>1.99</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>4</td>\n",
       "        <td>24</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Egg with shmear</td>\n",
       "        <td>2.39</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>Egg with shmear</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>eBagel Expansion Pack</td>\n",
       "        <td>1.99</td>\n",
       "        <td>eBagel</td>\n",
       "        <td>eBagel Expansion Pack</td>\n",
       "        <td>eBagel</td>\n",
       "        <td>1</td>\n",
       "        <td>137</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>0.99</td>\n",
       "        <td>BAGEL CORP</td>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>BAGEL CORP</td>\n",
       "        <td>2</td>\n",
       "        <td>12</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>0.99</td>\n",
       "        <td>BAGEL CORP</td>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>BAGEL CORP</td>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Plain with shmear', 1.99, 'Bobs Bagels', 'Plain with shmear', 'Bobs Bagels', 1, 12, 28),\n",
       " ('Plain with shmear', 1.99, 'Bobs Bagels', 'Plain with shmear', 'Bobs Bagels', 4, 24, None),\n",
       " ('Egg with shmear', 2.39, 'Bobs Bagels', 'Egg with shmear', 'Bobs Bagels', 2, 6, 47),\n",
       " ('eBagel Expansion Pack', 1.99, 'eBagel', 'eBagel Expansion Pack', 'eBagel', 1, 137, 5),\n",
       " ('Plain with shmear', 0.99, 'BAGEL CORP', 'Plain with shmear', 'BAGEL CORP', 2, 12, 24),\n",
       " ('Plain with shmear', 0.99, 'BAGEL CORP', 'Plain with shmear', 'BAGEL CORP', 3, 1, 17)]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "FROM bagel b, purchase p\n",
    "WHERE b.name = p.bagel_name AND b.made_by = p.franchise;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(*Aside: what would we have gotten if we had left out `AND b.made_by = p.franchise` in the WHERE clause?  Breaking down a complex query into its component steps is the best way to debug things like this!*)\n",
    "\n",
    "Next, the GROUP-BY is applied:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>prices</th>\n",
       "        <th>made_bys</th>\n",
       "        <th>bagel_name</th>\n",
       "        <th>franchises</th>\n",
       "        <th>dates</th>\n",
       "        <th>quantities</th>\n",
       "        <th>purchaser_ages</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Egg with shmear</td>\n",
       "        <td>2.39</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>Egg with shmear</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>1.99,1.99,0.99,0.99</td>\n",
       "        <td>Bobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORP</td>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>Bobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORP</td>\n",
       "        <td>1,4,2,3</td>\n",
       "        <td>12,24,12,1</td>\n",
       "        <td>28,24,17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>eBagel Expansion Pack</td>\n",
       "        <td>1.99</td>\n",
       "        <td>eBagel</td>\n",
       "        <td>eBagel Expansion Pack</td>\n",
       "        <td>eBagel</td>\n",
       "        <td>1</td>\n",
       "        <td>137</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Egg with shmear', '2.39', 'Bobs Bagels', 'Egg with shmear', 'Bobs Bagels', '2', '6', '47'),\n",
       " ('Plain with shmear', '1.99,1.99,0.99,0.99', 'Bobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORP', 'Plain with shmear', 'Bobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORP', '1,4,2,3', '12,24,12,1', '28,24,17'),\n",
       " ('eBagel Expansion Pack', '1.99', 'eBagel', 'eBagel Expansion Pack', 'eBagel', '1', '137', '5')]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    b.name,\n",
    "    GROUP_CONCAT(b.price, ',') AS prices,\n",
    "    GROUP_CONCAT(b.made_by, ',') AS made_bys,\n",
    "    bagel_name, \n",
    "    GROUP_CONCAT(p.franchise, ',') AS franchises,\n",
    "    GROUP_CONCAT(p.date, ',') AS dates,\n",
    "    GROUP_CONCAT(p.quantity, ',') AS quantities,\n",
    "    GROUP_CONCAT(p.purchaser_age, ',') AS purchaser_ages\n",
    "FROM bagel b, purchase p\n",
    "WHERE b.name = p.bagel_name AND b.made_by = p.franchise\n",
    "GROUP BY b.name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Note that the `GROUP_CONCAT` functions above are just for nicer display, as otherwise SQLite will choose a single arbitrary relation's values to display for the column in such a query*\n",
    "\n",
    "Next, the `HAVING` condition is applied:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>prices</th>\n",
       "        <th>made_bys</th>\n",
       "        <th>bagel_name</th>\n",
       "        <th>franchises</th>\n",
       "        <th>dates</th>\n",
       "        <th>total_quantity</th>\n",
       "        <th>purchaser_ages</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>1.99,1.99,0.99,0.99</td>\n",
       "        <td>Bobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORP</td>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>Bobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORP</td>\n",
       "        <td>1,4,2,3</td>\n",
       "        <td>49</td>\n",
       "        <td>28,24,17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>eBagel Expansion Pack</td>\n",
       "        <td>1.99</td>\n",
       "        <td>eBagel</td>\n",
       "        <td>eBagel Expansion Pack</td>\n",
       "        <td>eBagel</td>\n",
       "        <td>1</td>\n",
       "        <td>137</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Plain with shmear', '1.99,1.99,0.99,0.99', 'Bobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORP', 'Plain with shmear', 'Bobs Bagels,Bobs Bagels,BAGEL CORP,BAGEL CORP', '1,4,2,3', 49, '28,24,17'),\n",
       " ('eBagel Expansion Pack', '1.99', 'eBagel', 'eBagel Expansion Pack', 'eBagel', '1', 137, '5')]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    b.name,\n",
    "    GROUP_CONCAT(b.price, ',') AS prices,\n",
    "    GROUP_CONCAT(b.made_by, ',') AS made_bys,\n",
    "    bagel_name, \n",
    "    GROUP_CONCAT(p.franchise, ',') AS franchises,\n",
    "    GROUP_CONCAT(p.date, ',') AS dates,\n",
    "    SUM(p.quantity) AS total_quantity,\n",
    "    GROUP_CONCAT(p.purchaser_age, ',') AS purchaser_ages\n",
    "FROM bagel b, purchase p\n",
    "WHERE b.name = p.bagel_name AND b.made_by = p.franchise\n",
    "GROUP BY b.name\n",
    "HAVING SUM(p.quantity) > 12;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And finally, the aggregates and other operations in the select clause (projection) are applied:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>84.50999999999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>eBagel Expansion Pack</td>\n",
       "        <td>272.63</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Plain with shmear', 84.50999999999999), ('eBagel Expansion Pack', 272.63)]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT b.name, SUM(b.price * p.quantity) AS sales\n",
    "FROM bagel b, purchase p\n",
    "WHERE b.name = p.bagel_name AND b.made_by = p.franchise\n",
    "GROUP BY b.name\n",
    "HAVING SUM(p.quantity) > 12;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Quantifiers: existential and universal\n",
    "------------------------------------\n",
    "\n",
    "A massive amount of user interviewing has suggested that something called \"shmear\" (or *schmear*) is of critical importance to market success.  You decide to look for competitors that have some shmear on the menu:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>made_by</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bobs Bagels</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BAGEL CORP</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bobs Bagels',), ('BAGEL CORP',)]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT DISTINCT made_by FROM bagel WHERE name LIKE '%shmear%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A condition of this form (*\"there exists some r s.t. C(r)\"*) is known as an **existential** quantifier.  As is apparent above, these are fairly easy to write in SQL.  A **universal** quantifier on the other hand (of the form *\"C(r) for all r\"*) is a bit harder but still simple enough.\n",
    "\n",
    "So, for example, to find competitors with products that *all* have shmear in them:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>made_by</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bobs Bagels</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Bobs Bagels',)]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT made_by\n",
    "FROM bagel\n",
    "WHERE made_by NOT IN (\n",
    "    SELECT made_by\n",
    "    FROM bagel\n",
    "    WHERE name NOT LIKE '%shmear%');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "NULL values in SQL\n",
    "-----------------\n",
    "\n",
    "Let's look at an odd pair of queries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>bagel_name</th>\n",
       "        <th>franchise</th>\n",
       "        <th>date</th>\n",
       "        <th>quantity</th>\n",
       "        <th>purchaser_age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>1</td>\n",
       "        <td>12</td>\n",
       "        <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Egg with shmear</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>BAGEL CORP</td>\n",
       "        <td>2</td>\n",
       "        <td>12</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>BAGEL CORP</td>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>4</td>\n",
       "        <td>24</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Plain with shmear', 'Bobs Bagels', 1, 12, 28),\n",
       " ('Egg with shmear', 'Bobs Bagels', 2, 6, 47),\n",
       " ('Plain with shmear', 'BAGEL CORP', 2, 12, 24),\n",
       " ('Plain with shmear', 'BAGEL CORP', 3, 1, 17),\n",
       " ('Plain with shmear', 'Bobs Bagels', 4, 24, None)]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM purchase WHERE bagel_name LIKE '%shmear%';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>bagel_name</th>\n",
       "        <th>franchise</th>\n",
       "        <th>date</th>\n",
       "        <th>quantity</th>\n",
       "        <th>purchaser_age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>1</td>\n",
       "        <td>12</td>\n",
       "        <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Egg with shmear</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>BAGEL CORP</td>\n",
       "        <td>2</td>\n",
       "        <td>12</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>BAGEL CORP</td>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Plain with shmear', 'Bobs Bagels', 1, 12, 28),\n",
       " ('Egg with shmear', 'Bobs Bagels', 2, 6, 47),\n",
       " ('Plain with shmear', 'BAGEL CORP', 2, 12, 24),\n",
       " ('Plain with shmear', 'BAGEL CORP', 3, 1, 17)]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT * FROM purchase \n",
    "WHERE bagel_name LIKE '%shmear%' \n",
    "  AND (purchaser_age >= 5 OR purchaser_age < 5);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that `NULL` values are treated specially.  In SQL, there are actually three effective boolean values- `TRUE`, `FALSE`, and `UNKNOWN`.  Any comparison operation between a `NULL` value and a constant will return `UNKNOWN`- and in SQL, relations are only passed through when the condition over them returns `TRUE`.  We can of course handle them specially though:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>bagel_name</th>\n",
       "        <th>franchise</th>\n",
       "        <th>date</th>\n",
       "        <th>quantity</th>\n",
       "        <th>purchaser_age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>1</td>\n",
       "        <td>12</td>\n",
       "        <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Egg with shmear</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>BAGEL CORP</td>\n",
       "        <td>2</td>\n",
       "        <td>12</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>BAGEL CORP</td>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "        <td>Bobs Bagels</td>\n",
       "        <td>4</td>\n",
       "        <td>24</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Plain with shmear', 'Bobs Bagels', 1, 12, 28),\n",
       " ('Egg with shmear', 'Bobs Bagels', 2, 6, 47),\n",
       " ('Plain with shmear', 'BAGEL CORP', 2, 12, 24),\n",
       " ('Plain with shmear', 'BAGEL CORP', 3, 1, 17),\n",
       " ('Plain with shmear', 'Bobs Bagels', 4, 24, None)]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT * FROM purchase\n",
    "WHERE bagel_name LIKE '%shmear%'\n",
    "  AND (purchaser_age >= 5 OR purchaser_age < 5 \n",
    "       OR purchaser_age IS NULL);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What happens when there are nulls in a join?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Egg with shmear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>eBagel Expansion Pack</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Plain with shmear',), ('Egg with shmear',), ('eBagel Expansion Pack',)]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT DISTINCT b.name \n",
    "FROM bagel b, purchase p \n",
    "WHERE b.name = p.bagel_name AND b.made_by = p.franchise;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We're missing bagels which were never purchased!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inner and Outer Joins\n",
    "--------------------\n",
    "\n",
    "A join query using a `WHERE` clause like the one just showed is actually an `INNER JOIN`, and can also be written as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Egg with shmear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>eBagel Expansion Pack</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Plain with shmear',), ('Egg with shmear',), ('eBagel Expansion Pack',)]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT DISTINCT b.name \n",
    "FROM bagel b\n",
    "    INNER JOIN purchase p ON b.name = p.bagel_name AND b.made_by = p.franchise;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An `INNER JOIN` on tables `A` and `B` with join condition `C(A,B)` returns only relations `(a,b)` such that `C(a,b) = TRUE`.  If, as in our example above, there is no `b` such that `C(a,b)` is true, then `a` is simply not returned in thr output multiset.\n",
    "\n",
    "We can use an `OUTER JOIN` instead, however, which comes in three varieties: `LEFT`, `RIGHT`, and `FULL`. \n",
    "\n",
    "In our current situation, what we needed was a `LEFT OUTER JOIN`.  A left outer join will also return `(a, NULL)` for left relations `a` such that there is no `b` for which `C(a,b) = TRUE`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite://\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Plain with shmear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Egg with shmear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>eBagel Drinkable Bagel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>eBagel Expansion Pack</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Organic Flax-seed bagel chips</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Plain with shmear',),\n",
       " ('Egg with shmear',),\n",
       " ('eBagel Drinkable Bagel',),\n",
       " ('eBagel Expansion Pack',),\n",
       " ('Organic Flax-seed bagel chips',)]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT DISTINCT b.name \n",
    "FROM bagel b\n",
    "    LEFT OUTER JOIN purchase p ON b.name = p.bagel_name AND b.made_by = p.franchise;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
